/*
 * 文件名：Excel2007Util.java
 * 版权：Copyright by http://www.znjf.com
 * 描述：
 * 修改人：Awoke
 * 修改时间：2018-5-25
 * 跟踪单号：
 * 修改单号：
 * 修改内容：
 */

package top.infopub.mgr.util;


import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * Excel2007+ 文档工具类
 * @author Awoke
 * @version 2018年5月25日
 * @see Excel2007Util
 * @since
 */
public final class Excel2007Util {

    /**
     * 日志
     */
    private static final Logger logger = LoggerFactory.getLogger(Excel2007Util.class);

    /**
     * 导出excel
     * @param list 导出的数据集合
     * @param key_name_width (list-map中的 key)+(excel列名 name)+(excel列宽 width) ，使用"|"连接，width默认3000，key name必填
     * @param fileName 导出的excel文件名
     * @param response
     * @see
     */
    public static void exportExcel(List<Map<String, Object>> list, String[] key_name_width,
                                   String fileName, HttpServletResponse response) {

        int length = key_name_width.length;
        String[] keys = new String[length];
        String[] columnNames = new String[length];
        Short[] columnWidths = new Short[length];
        for (int i = 0; i < length; i++ ) {
            String[] array = key_name_width[i].split("\\|");
            keys[i] = array[0];
            columnNames[i] = array[1];
            // 只有key name时，设置默认宽度3000
            if (array.length == 2) {
                columnWidths[i] = 3000;
            }
            else {
                columnWidths[i] = Short.valueOf(array[2]);
            }
        }
        exportExcel(list, keys, columnNames, columnWidths, fileName, response);
    }

    /**
     * 导出excel
     * @param list 导出的数据集合
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * @param columnWidths 列宽度
     * @param fileName 导出的excel文件名
     * @param response
     * @see
     */
    public static void exportExcel(List<Map<String, Object>> list, String[] keys,
                                   String[] columnNames, Short[] columnWidths, String fileName,
                                   HttpServletResponse response) {

        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        ByteArrayOutputStream os = null;
        InputStream is = null;
        try {
            os = new ByteArrayOutputStream();
            // 创建excel工作簿
            createWorkBook(list, keys, columnNames, columnWidths, fileName).write(os);

            byte[] content = os.toByteArray();
            is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            // response.setContentType("application/vnd.ms-excel;charset=utf-8");//2003
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                                                      + new String((fileName).getBytes(),
                                                          "iso-8859-1") + ".xlsx");
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(response.getOutputStream());
            byte[] buff = new byte[1024 * 8];
            int bytesRead = -1;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bos.flush();
            response.flushBuffer();
        }
        catch (Exception e) {
            logger.error("exportExcel error ", e);
        }
        finally {
            IOUtils.closeQuietly(is);
            IOUtils.closeQuietly(bis);
            IOUtils.closeQuietly(bos);
            IOUtils.closeQuietly(os);
        }
    }

    /**
     * 下载excel模板
     * @param path  模板excel路径
     * @param response 
     * @see
     */
    public static void downloadExcelTemplet(String filePath, HttpServletResponse response) {
        InputStream fis = null;
        OutputStream fos = null;
        try {
            String ext = FilenameUtils.getExtension(filePath).toLowerCase();
            String mimeType = FileHelper.keyTip.get(ext).getMimeType();
            File file = new File(filePath);
            String fileName =  FilenameUtils.getBaseName(filePath);
            response.reset(); // 设置response的Header
            response.setContentType(mimeType + ";charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + "."
                                                      + ext);
            response.setContentLength((int)file.length());

            fis = new BufferedInputStream(new FileInputStream(filePath));
            fos = new BufferedOutputStream(response.getOutputStream());
            byte[] buffer = new byte[1024 * 8];
            int read = -1;
            while (-1 != (read = fis.read(buffer, 0, buffer.length))) {
                fos.write(buffer, 0, read);
            }
            fos.flush();
            response.flushBuffer();
        }
        catch (FileNotFoundException e) {
            logger.error("FileNotFoundException --> filePath = " + filePath);
        }
        catch (IOException e) {
            logger.error("IOException --> filePath = " + filePath);
        }
        finally {
            IOUtils.closeQuietly(fis);
            IOUtils.closeQuietly(fos);
        }
    }

    /**
     * 创建excel WorkBook
     * @param list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * @param columnWidths 列宽度
     * @param model sheet名称
     *
     * */
    private static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys,
                                           String[] columnNames, Short[] columnWidths, String model) {
        // 创建excel工作簿
        Workbook wb = new SXSSFWorkbook();
        // 创建第一个sheet（页），并命名
        SXSSFSheet sheet = (SXSSFSheet)wb.createSheet(model);
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++ ) {
            sheet.setColumnWidth((short)i, columnWidths[i]);
        }
        // 创建第一行
        SXSSFRow row = (SXSSFRow)sheet.createRow((short)0);

        // 创建两种单元格格式
        XSSFCellStyle cs = (XSSFCellStyle)wb.createCellStyle();
        XSSFCellStyle cs2 = (XSSFCellStyle)wb.createCellStyle();
        // 创建两种字体
        XSSFFont f = (XSSFFont)wb.createFont();
        XSSFFont f2 = (XSSFFont)wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short)10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short)10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //设置列名
        for (int i = 0; i < columnNames.length; i++ ) {
            SXSSFCell cell = (SXSSFCell)row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //数值型格式
        CellStyle cellStyleTmp_number = wb.createCellStyle();
        cellStyleTmp_number.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.0000000000"));
        cellStyleTmp_number.setFont(f2);
        cellStyleTmp_number.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleTmp_number.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleTmp_number.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleTmp_number.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleTmp_number.setAlignment(CellStyle.ALIGN_CENTER);

        //年月日 日期格式
        CellStyle cellStyleTmp_date = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        cellStyleTmp_date.setDataFormat(format.getFormat("yyyy-MM-dd"));
        cellStyleTmp_date.setFont(f2);
        cellStyleTmp_date.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleTmp_date.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleTmp_date.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleTmp_date.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleTmp_date.setAlignment(CellStyle.ALIGN_CENTER);

        /** 这里设置excel的合并单元格(这里只支持需要合并的列数为1的合并单元格)
         *  用法:在sql中查询出要合并的列依据字段COMBINEINDEX,列标(第一列从0开始)COLINDEX 
         *  ex: select ......, id combineIndex, 1 COLINDEX ......(这里1表示要合并的列是第2列)
         */
        setMergedRegion(list, sheet);

        //设置每行每列的值
        for (int i = 0; i < list.size(); i++ ) {
            SXSSFRow row1 = (SXSSFRow)sheet.createRow(i + 1);
            row1.setHeight((short)300);
            // 在row行上创建一个方格
            for (int j = 0; j < keys.length; j++ ) {
                SXSSFCell cell = (SXSSFCell)row1.createCell(j);
                Object tmpValue = list.get(i).get(keys[j]);
                if (tmpValue instanceof BigDecimal) {
                    cell.setCellValue(tmpValue == null ? 0D : ((BigDecimal)tmpValue).doubleValue());
                    cell.setCellStyle(cellStyleTmp_number);
                }
                else if (tmpValue instanceof Date) {
                    cell.setCellValue((Date)tmpValue);
                    cell.setCellStyle(cellStyleTmp_date);
                }
                else {
                    cell.setCellValue(tmpValue == null ? "" : tmpValue.toString());
                    cell.setCellStyle(cs2);
                }
            }
        }
        return wb;
    }

    private static void setMergedRegion(List<Map<String, Object>> list, SXSSFSheet sheet) {
        if (list.size() > 1 && list.get(0) != null && list.get(0).get("COLINDEX") != null
            && list.get(0).get("COMBINEINDEX") != null) {
            String combineIndex = null;
            // 上一次是否做了合并
            boolean combineIf = false;
            int rowStart = 1, rowEnd = 1, colStart = Integer.parseInt(((BigDecimal)list.get(0).get(
                "COLINDEX")).toString()), colEnd = colStart;// 合并单元格方法需要的四参数
            String colindexno = list.get(0).get("COLINDEXNO") == null
                                || "".equals(list.get(0).get("COLINDEXNO")) ? null : list.get(0).get(
                "COLINDEXNO").toString();
            Set<Integer> iset = new HashSet<Integer>();
            int colindexall = 0;
            if (colindexno != null) {
                colindexall = Integer.parseInt(((BigDecimal)list.get(0).get("COLINDEXALL")).toString());
                String[] colindexnoArray = colindexno.split("-");
                for (String s : colindexnoArray) {
                    iset.add(Integer.parseInt(s));
                }
            }
            for (int i = 0; i < list.size(); i++ ) {
                String thisCombineIndex = (String)list.get(i).get("COMBINEINDEX");
                if (thisCombineIndex != null && thisCombineIndex.equals(combineIndex)) {
                    if (!combineIf) {
                        rowStart = i;
                    }
                    combineIf = true;
                    if (i == list.size() - 1 && combineIf) {
                        rowEnd = list.size();
                        // 最后一条记录合并
                        if (colindexno != null) {
                            for (int x = colStart; x <= colindexall; x++ ) {
                                if (!iset.contains(x)) {
                                    sheet.addMergedRegion(new CellRangeAddress(rowStart, rowEnd,
                                        x, x));
                                }
                            }
                        }
                        else {
                            sheet.addMergedRegion(new CellRangeAddress(rowStart, rowEnd, colStart,
                                colEnd));
                        }
                    }
                }
                else {
                    rowEnd = i;
                    if (combineIf) {
                        // 出现依据字段不一样做一次合并
                        if (colindexno != null) {
                            for (int x = colStart; x <= colindexall; x++ ) {
                                if (!iset.contains(x)) {
                                    sheet.addMergedRegion(new CellRangeAddress(rowStart, rowEnd,
                                        x, x));
                                }
                            }
                        }
                        else {
                            sheet.addMergedRegion(new CellRangeAddress(rowStart, rowEnd, colStart,
                                colEnd));
                        }
                    }
                    combineIf = false;
                }
                combineIndex = thisCombineIndex;
            }
        }
    }
}
